﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Generic;

/// <summary>
/// Summary description for FacilityDAO
/// </summary>
public class FacilityDAO:DBAccess
{
	#region sql Commands   
    string SQL_SELECT_ID = "select @@IDENTITY from Facility";
    string SQL_SELECT_ALL = "select * from Facility";
    string SQL_SELECT_BY_ID = "select * from Facility where ID=@id";
    string SQL_SELECT_FAC_COUNT = "select count(*) from Facility";
    string SQL_INSERT = "insert into Facility(Name,Description,imageURL,Article, Price, Discount, TypeID,State, ModifiedDate,ModifiedBy) values(@Name,@Description,@imageURL,@Article, @Price, @Discount, @TypeID,@State, @ModifiedDate,@ModifiedBy)";
    string SQL_UPDATE = "update Facility set Name = @Name, Description = @Description, imageURL = @imageURL, Article = @Article, Price = @Price, Discount = @Discount, TypeID = @TypeID, State=@State,ModifiedDate=@ModifiedDate,ModifiedBy=@ModifiedBy where ID = @id";
    string SQL_DELETE = "delete from Facility where ID = @id";
    string SQL_SELECT_FAC_BY_LOC = "select ID, [Name], Description,Availability from Facility"
+" where ID in (select FacilityID from SupportedFacilities "
	+" where DeparturePlaceID=@f_loc and ArrivalPlaceID=@s_loc"
	+" or DeparturePlaceID=@s_loc and ArrivalPlaceID=@f_loc)"
 +" and State =1 order by [TypeID]";
    #endregion

    #region fields
    string message = "";
    #endregion

    #region constructors
    public FacilityDAO()
    {

    }
    #endregion

    #region properties
    public string Message
    {
        get { return message; }
        set { message = value; }
    }
    #endregion

    #region Methos
    /// <summary>
    /// Get Facility by id
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public Facility GetFacility(int id)
    {
        Facility fac = new Facility();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_BY_ID, conn);
            if (Connect())
            {
                command.Parameters.AddWithValue("@id", id);
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    fac.Id = dr.GetInt32(0);
                    fac.Name = dr.GetString(1);
                    fac.Description = dr.GetString(2);
                    fac.ImageURL = dr.GetString(3);
                    fac.Article = dr.GetString(4);
                    fac.Price = dr.GetDecimal(5);
                    fac.Discount = dr.GetDecimal(6);
                    fac.Type.Id = dr.GetInt32(7);  
                    fac.State = dr.GetBoolean(8);                                     
                    fac.ModifiedDate = dr.GetDateTime(9);
                    fac.ModifiedBy = dr.GetInt32(10);
                    
                }
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return fac;
    }
    /// <summary>
    /// Get all facilities 
    /// </summary>
    /// <returns></returns>
   public IList<Facility> GetAllFacilities()
    {
        IList<Facility> list = new List<Facility>();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_ALL, conn);
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Facility fac = new Facility();
                    fac.Id = dr.GetInt32(0);
                    fac.Name = dr.GetString(1);
                    fac.Description = dr.GetString(2);
                    fac.ImageURL = dr.GetString(3);
                    fac.Article = dr.GetString(4);
                    fac.Price = dr.GetDecimal(5);
                    fac.Discount = dr.GetDecimal(6);
                    fac.Type.Id = dr.GetInt32(7);
                    fac.ModifiedDate = dr.GetDateTime(8);
                    fac.ModifiedBy = dr.GetInt32(9);
                    list.Add(fac);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }
   /// <summary>
   /// Insert new facility into database
   /// </summary>
   /// <param name="fac"></param>
   /// <returns></returns>
   public int Insert(Facility fac)
   {
       int effectedRows = 0;
       try
       {
           SqlCommand command = new SqlCommand(SQL_INSERT, conn);
           command.Parameters.AddWithValue("@Name", fac.Name);
           command.Parameters.AddWithValue("@Description", fac.Description);
           command.Parameters.AddWithValue("@imageURL", fac.ImageURL);
           command.Parameters.AddWithValue("@Article", fac.Article);
           command.Parameters.AddWithValue("@Price", fac.Price);
           command.Parameters.AddWithValue("@Discount", fac.Discount);
           command.Parameters.AddWithValue("@TypeID", fac.Type.Id);
           command.Parameters.AddWithValue("@State", fac.State); 
           command.Parameters.AddWithValue("@ModifiedDate", fac.ModifiedDate);
           command.Parameters.AddWithValue("@ModifiedBy", fac.ModifiedBy);           
           if (Connect())
           {
               effectedRows = command.ExecuteNonQuery();
               command.CommandText = "Select @@IDENTITY from Facility";
               fac.Id = int.Parse(command.ExecuteScalar().ToString());
               Disconnect();
           }
       }
       catch (SqlException ex) { message = ex.Message; }
       catch (Exception ex) { message = ex.Message; }
       return effectedRows;
   }

   /// <summary>
   /// Update Facility
   /// </summary>
   /// <param name="fac"></param>
   /// <returns></returns>
   public int Update(Facility fac)
   {
       int effectedRows = 0;
       try
       {
           SqlCommand command = new SqlCommand(SQL_UPDATE, conn);
           command.Parameters.AddWithValue("@Name", fac.Name);
           command.Parameters.AddWithValue("@Description", fac.Description);
           command.Parameters.AddWithValue("@imageURL", fac.ImageURL);
           command.Parameters.AddWithValue("@Article", fac.Article);
           command.Parameters.AddWithValue("@Price", fac.Price);
           command.Parameters.AddWithValue("@Discount", fac.Discount);
           command.Parameters.AddWithValue("@TypeID", fac.Type.Id);
           command.Parameters.AddWithValue("@State", fac.State);
           command.Parameters.AddWithValue("@ModifiedDate", fac.ModifiedDate);
           command.Parameters.AddWithValue("@ModifiedBy", fac.ModifiedBy);  
           command.Parameters.AddWithValue("@id", fac.Id);           
           if (Connect())
           {
               effectedRows = command.ExecuteNonQuery();
               Disconnect();
           }
       }
       catch (SqlException ex) { message = ex.Message; }
       catch (Exception ex) { message = ex.Message; }
       return effectedRows;
   }

   /// <summary>
   /// Remove facility out of database
   /// </summary>
   /// <param name="fac"></param>
   /// <returns></returns>
   public int Delete(int id)
   {
       int effectedRows = 0;
       try
       {
           SqlCommand command = new SqlCommand(SQL_DELETE, conn);
           command.Parameters.AddWithValue("@id", id);
           if (Connect())
           {
               effectedRows = command.ExecuteNonQuery();
               Disconnect();
           }
       }
       catch (SqlException ex) { message = ex.Message; }
       catch (Exception ex) { message = ex.Message; }
       return effectedRows;
   }
    /// <summary>
    /// Get list of faciliies with paging
    /// </summary>
    /// <param name="currentPage"></param>
    /// <param name="pageSize"></param>
    /// <param name="total"></param>
    /// <returns></returns>
    public IList<Facility> GetFacilities(int currentPage, int pageSize, ref int total,byte state)
    {
        IList<Facility> list = new List<Facility>();
        try
        {
            SqlCommand command = new SqlCommand("sp_SelectFacilities", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@state", SqlDbType.TinyInt);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@state"].Value = state;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Facility fac = new Facility();
                    fac.Id = dr.GetInt32(1);
                    fac.Name = dr.GetString(2);
                    fac.Description = dr.GetString(3);
                    fac.ImageURL = dr.GetString(4);
                    fac.Price = dr.GetDecimal(5);
                    fac.Discount = dr.GetDecimal(6);
                    fac.State = dr.GetBoolean(7);
                    list.Add(fac);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }

    public IList<Facility> GetFacilitiesExt(int currentPage, int pageSize, ref int total, string keyword,byte state)
    {
        IList<Facility> list = new List<Facility>();
        try
        {
            SqlCommand command = new SqlCommand("sp_SelectFacilitiesExt", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@keyword", SqlDbType.NVarChar);
            command.Parameters.Add("@state", SqlDbType.TinyInt);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@keyword"].Value = keyword;
            command.Parameters["@state"].Value = state;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Facility fac = new Facility();
                    fac.Id = dr.GetInt32(1);
                    fac.Name = dr.GetString(2);
                    fac.Description = dr.GetString(3);
                    fac.Price = dr.GetDecimal(4);
                    fac.Discount = dr.GetDecimal(5);
                    fac.State = dr.GetBoolean(6);
                    list.Add(fac);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }

    public IList<Facility> GetFacilitiesAdv(int currentPage, int pageSize, ref int total, string keyword, int typeId, decimal lowPrice, decimal highPrice, byte state)
    {
        IList<Facility> list = new List<Facility>();
        try
        {
            SqlCommand command = new SqlCommand("sp_SelectFacilitiesAdv", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@keyword", SqlDbType.NVarChar);
            command.Parameters.Add("@typeId", SqlDbType.Int);
            command.Parameters.Add("@lowPrice", SqlDbType.Money);
            command.Parameters.Add("@highPrice", SqlDbType.Money);
            command.Parameters.Add("@state", SqlDbType.TinyInt);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@keyword"].Value = keyword;
            command.Parameters["@typeId"].Value = typeId;
            command.Parameters["@lowPrice"].Value = lowPrice;
            command.Parameters["@highPrice"].Value = highPrice;
            command.Parameters["@state"].Value = state;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Facility fac = new Facility();
                    fac.Id = dr.GetInt32(1);
                    fac.Name = dr.GetString(2);
                    fac.Description = dr.GetString(3);
                    fac.ImageURL = dr.GetString(4);
                    fac.Price = dr.GetDecimal(5);
                    fac.Discount = dr.GetDecimal(6);
                    fac.State = dr.GetBoolean(7);
                    list.Add(fac);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }
    /// <summary>
    /// Get supported facilites between places
    /// </summary>
    /// <param name="firstLocId"></param>
    /// <param name="secondLocId"></param>
    /// <returns></returns>
    public IList<Facility> GetFacilitiesByLocation(int firstLocId, int secondLocId)
    {
        IList<Facility> list = new List<Facility>();
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_SELECT_FAC_BY_LOC, conn);
            cmd.Parameters.AddWithValue("@f_loc",firstLocId);
            cmd.Parameters.AddWithValue("@s_loc", secondLocId);
            if (Connect())
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Facility fac = new Facility();
                    fac.Id = dr.GetInt32(0);
                    fac.Name = dr.GetString(1);
                    fac.Description = dr.GetString(2);
                    fac.Availability = dr.GetInt32(3);
                    list.Add(fac);
                }
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }

    /// <summary>
    /// Get number of facilities
    /// </summary>
    /// <returns></returns>
    public int GetFacilityCount()
    {
        int quantity = 0;
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_FAC_COUNT, conn);
            if (Connect())
            {
                quantity = int.Parse(command.ExecuteScalar().ToString());
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return quantity;

    }
    #endregion
}